package com.jdbc;

import java.sql.*;
import java.util.Random;
import java.util.Scanner;

import static com.jdbc.JDBCDemo.testPreparedStatement;
import static com.jdbc.JDBCDemo.testQuery;

/**
 * @author lenovo
 * @Description
 * @Date2025-2-24 16:40
 */
public class LoginDemo {
    public static final String URL = "jdbc:mysql://localhost:3306/mysqldb?serverTimezone=GMT";
    public static final String USER = "root";
    public static final String PASSWORD = "123456";


    public static boolean login(String username, String password) {
        //1.获取连接
        try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) {
            //2.定义sql
            String sql = "select * from t_user where username ='" + username + "' and password='" + password + "'";
            //3.获取命令对象
            Statement statement = connection.createStatement();
            //4.执行查询命令
            ResultSet resultSet = statement.executeQuery(sql);
            //5.判断是否有匹配的账号密码
            if (resultSet.next()) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入用户名：");
        String username = scanner.nextLine();
        System.out.println("请输入密码：");
        String password = scanner.nextLine();
        if (login(username, password)) {
            System.out.println("登录成功");
            printMenu();
            while (true) {
                String choice = scanner.nextLine();
                switch (choice) {
                    case "1":
                        selectStudent();
                        break;
                    case "2":
                        insertStudent();
                        break;
                    case "3":
                    deleteStudent();
                    break;
                    case "4":
                    updateStudent();
                    break;
                    case "5":
                        System.out.println("退出系统。");
                        return;
                }
            }
        } else {
            System.out.println("登录失败");
        }
    }

//    public static boolean login2(String username, String password) {
//        //1.获取连接
//        try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) {
//            //2.定义sql
//            String sql = "select * from t_user where username =? and password=?";
//            //3.获取命令对象
//            PreparedStatement statement = connection.prepareStatement(sql);
//            //4.执行查询命令
//            statement.setString(1, username);
//            statement.setString(2, password);
//            //5.执行查询命令
//            ResultSet resultSet = statement.executeQuery();
//            //6.判断是否有匹配的账号密码
//            if (resultSet.next()) {
//                return true;
//            }
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//        return false;
//    }

    private static void printMenu() {
        System.out.println("\n选择菜单：");
        System.out.println("1）打印所有学生信息");
        System.out.println("2）添加学生");
        System.out.println("3）删除学生");
        System.out.println("4）输出学生信息");
        System.out.println("5）退出系统");
        System.out.print("请输入你的选择：");
    }

    public static void selectStudent(){
        //通过驱动管理器获得连接
        try (Connection connection = DriverManager.getConnection(URL,USER,PASSWORD);){
            //通过连接对象获取命令对象
            Statement statement = connection.createStatement();
            //实现数据查询
            String sql="select *  from student_s";
            ResultSet resultSet= statement.executeQuery(sql);
            //遍历结果集
            while (resultSet.next()) {
                //读取每列数据
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                String cellphone = resultSet.getString("cellphone");
                String address = resultSet.getString("address");
                String gender = resultSet.getString("gender");
                System.out.println("id:" + id + " name:" + name + " age:" + age + " celphone:" + cellphone + " address:" + address + " gender:" + gender);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


    public static void insertStudent(){
        Scanner scanner = new Scanner(System.in);

        System.out.println("请输入学生姓名");
        String name = scanner.nextLine();

        System.out.println("请输入学生年龄");
        Integer age = scanner.nextInt();
        scanner.nextLine();

        System.out.println("请输入学生电话");
        String cellphone = scanner.nextLine();

        System.out.println("请输入学生地区");
        String address = scanner.nextLine();

        System.out.println("请输入学生性别");
        String gender = scanner.nextLine();
        //通过驱动管理器获得连接
        try (Connection connection = DriverManager.getConnection(URL,USER,PASSWORD);){
            System.out.println(connection);
            //通过连接对象获取命令对象
            String sql="insert into student_s(name,age,cellphone,address,gender) values(?,?,?,?,?)";
            PreparedStatement preparedStatement=connection.prepareStatement(sql);
            //设置参数
            preparedStatement.setString(1,name);
            preparedStatement.setInt(2,age);
            preparedStatement.setString(3,cellphone);
            preparedStatement.setString(4,address);
            preparedStatement.setString(5,gender);
            //执行命令
            int rows= preparedStatement.executeUpdate();
            if(rows>0){
                System.out.println("数据插入成功");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    private static void deleteStudent() {
        Scanner scanner = new Scanner(System.in);
        System.out.print("请输入要删除的学生ID：");
        Integer id = scanner.nextInt();
        try (Connection connection = DriverManager.getConnection(URL,USER,PASSWORD);){
            System.out.println(connection);
            String sql = "delete FROM student_s WHERE id = ?";
            PreparedStatement preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            int rows= preparedStatement.executeUpdate();
            if(rows>0){
                System.out.println("数据删除成功");
            }
            System.out.println("学生删除成功！");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    private static void updateStudent() {
        Scanner scanner = new Scanner(System.in);
        System.out.print("请输入要更新的学生ID：");
        Integer id = scanner.nextInt();
        scanner.nextLine();

        System.out.println("请输入新学生姓名");
        String name = scanner.nextLine();

        System.out.println("请输入新学生年龄");
        Integer age = scanner.nextInt();
        scanner.nextLine();

        System.out.println("请输入新学生电话");
        String cellphone = scanner.nextLine();

        System.out.println("请输入新学生地区");
        String address = scanner.nextLine();

        System.out.println("请输入新学生性别");
        String gender = scanner.nextLine();

        try (Connection connection = DriverManager.getConnection(URL,USER,PASSWORD);){
            System.out.println(connection);
            String sql = "update student_s set name = ?,age = ?,cellphone = ?,address = ?,gender = ? WHERE id = ?";
            PreparedStatement preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            preparedStatement.setString(2,name);
            preparedStatement.setInt(3,age);
            preparedStatement.setString(4,cellphone);
            preparedStatement.setString(5,address);
            preparedStatement.setString(6,gender);
            int rows= preparedStatement.executeUpdate();
            if(rows>0){
                System.out.println("数据更新成功");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


}
